package com.ibuonline.b2b.common.importer;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.ibuonline.b2b.entity.ProdCustCategory;

public class ProdCustTidy {
	public static List<Integer> companyIds = new ArrayList<Integer>();
	private static String url = "jdbc:mysql://192.168.0.229:3306/b2b?characterEncoding=GBK";
	private static String user = "b2b";
	private static String password = "b2b";
	

	public static void getCompanyIds() {
		String selectSql = "select company_id from company WHERE company_id IN (SELECT company_id FROM prod_cust_category)";
		Connection conn = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(url, user, password);
			ResultSet rs = conn.createStatement().executeQuery(selectSql);
			while (rs.next()) {
				companyIds.add(rs.getInt("company_id"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void updateProdCustCategory(Integer companyId){
		 List<ProdCustCategory> prodCustList = new ArrayList<ProdCustCategory>();
		 List<ProdCustCategory> repeatProdCustList = new ArrayList<ProdCustCategory>();
		 List<Long> repeatProdCustIds = new ArrayList<Long>();
		 List<Long> prodCustIds =  new ArrayList<Long>();
		 String sqlAll = "SELECT id,name FROM prod_cust_category WHERE company_id = " + companyId + " AND " +
		 				 "parent_id = 0";
		 String sql = "SELECT min(id) as id, name FROM prod_cust_category WHERE company_id = " + companyId + " AND " +
				     "parent_id = 0 GROUP BY NAME HAVING COUNT(NAME) > 1 ORDER BY  display_order";
		 Connection conn = null;
		 try {
				Class.forName("org.gjt.mm.mysql.Driver");
				conn = DriverManager.getConnection(url, user, password);
				ResultSet rs = conn.createStatement().executeQuery(sqlAll);
				while (rs.next()) {
					ProdCustCategory prodCust = new ProdCustCategory();
					prodCust.setId(rs.getLong("id"));
					prodCust.setName(rs.getString("name"));
					prodCustList.add(prodCust);
				}
				rs = conn.createStatement().executeQuery(sql);
				while (rs.next()) {
					ProdCustCategory prodCust = new ProdCustCategory();
					prodCust.setId(rs.getLong("id"));
					prodCust.setName(rs.getString("name"));
					repeatProdCustList.add(prodCust);
				}
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
		}
		if (prodCustList.size()>0) {
			for (int i = 0;i<prodCustList.size();i++) {
				if (repeatProdCustList.size()>0) {
					for (int j = 0;j< repeatProdCustList.size();j++) {
						ProdCustCategory temp = prodCustList.get(i);
						if (temp.getName().equals(repeatProdCustList.get(j).getName()) 
							&& !temp.getId().equals(repeatProdCustList.get(j).getId())) {
							repeatProdCustIds.add(temp.getId());
							prodCustIds.add(repeatProdCustList.get(j).getId());
						}
					}
				} else {
					break;
				}
			}
		}
		if (repeatProdCustIds.size()>0) {
			deleteRepeatInfo(repeatProdCustIds,prodCustIds);
		}
	}
	public static void deleteRepeatInfo(List<Long> repeatProdCustIds,List<Long> prodCustIds){
		Connection conn = null;
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(url, user, password);
			for (int i=0;i<repeatProdCustIds.size();i++) { // ProdToCustCategory
				String selProdIdsSql = "select prod_id from prod_to_cust_category where custom_cat_id ="+repeatProdCustIds.get(i);
				List<Long> prodIds = new ArrayList<Long>();
				ResultSet rs = conn.createStatement().executeQuery(selProdIdsSql);
				while(rs.next()) {
					prodIds.add(rs.getLong("prod_id"));
				}
				if (prodIds.size()>0) {
					String delCatSql = "DELETE FROM  prod_to_cust_category WHERE custom_cat_id = " + repeatProdCustIds.get(i);
					conn.createStatement().execute(delCatSql);
					for (int j = 0;j<prodIds.size();j++) {
						String insertSql = "INSERT INTO prod_to_cust_category(prod_id,custom_cat_id) VALUES (" +
						prodIds.get(j)+","+ prodCustIds.get(i) +")";
						try{
							conn.createStatement().execute(insertSql);
						}catch (Exception e){
						}
					}
				}
				String sql = "DELETE FROM  prod_cust_category WHERE id = " + repeatProdCustIds.get(i);
				conn.createStatement().execute(sql);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	public static void main (String[] arge) {
		getCompanyIds();
		if (companyIds.size()>0){
			for (int i = 0 ;i<companyIds.size();i++) {
				System.out.println(companyIds.get(i));
				updateProdCustCategory(companyIds.get(i));
			}
		}
	}
}
